Execute SSIS Package from a SQL Server Stored Procedure

Problem

My application has a number of requirements for manipulating data and I would like to create SSIS packages to perform these tasks. However, I am unsure of the best way to execute an SSIS package from my application. Can you provide an example of how to do this from a stored procedure?

Solution

Based on your requirements, your application should call a stored procedure that executes an SSIS package. There are two ways to execute an SSIS package from a stored procedure:

• Use the extended stored procedure xp_cmdshell to execute the DTEXEC command line utility.
• In SSIS 2012 use the built-in stored procedures; e.g. ssisdb.catalog.create_execution, ssisdb.catalog.start_execution, etc.

In this tip I walk through an example of executing an SSIS package using the built-in stored procedures that come with SQL Server 2012. I will review the following steps:

• Create a sample SSIS package.
• Deploy the SSIS project using the default project deployment model in SSIS 2012.
• Create a stored procedure to execute the SSIS package.
• Execute the stored procedure in SQL Server Management Studio (SSMS).
• Create a .NET console application to execute the stored procedure.
• Run the .NET console application and check the results.

Create a Sample SSIS Package

I will use the following SSIS package:

The SSIS package performs the following steps:

• Creates a table named Test if it does not exist.
• Inserts a row into the Test table.

The package is deliberately simple since the focus of this tip is to execute an SSIS package from a stored procedure.

Deploy the SSIS Project

By default SSIS 2012 uses the new project deployment mode where an entire SSIS project is deployed to the SSISDB database. To deploy the project right click the project in the Solution Explorer, select Deploy from the menu, and fill in the dialog as shown below:

The Path is a combination of the following:

• /SSISDB is the database name; it is fixed and can not be overwritten at this time.
• mssqltips is the folder name; this is user-defined.
• exec-ssis-stored-proc-ssis-sample is the name of my project.

Note that you need to setup the SSISDB catalog; see SSIS Package Deployment Model in SQL Server 2012 for the details.

Create a Stored Procedure

I will use the following stored procedure in my mssqltips database to execute the SSIS package:

create procedure dbo.execute_ssis_package_sample
 @output_execution_id bigint output
as
begin
 declare @execution_id bigint
 exec ssisdb.catalog.create_execution 
  @folder_name = 'mssqltips'
 ,@project_name = 'exec-ssis-stored-proc-ssis-sample'
 ,@package_name = 'Sample.dtsx'
 ,@execution_id = @execution_id output
 exec ssisdb.catalog.start_execution @execution_id
 set @output_execution_id = @execution_id
end

The following are the main points about the above stored procedure:

• The stored procedure ssisdb.catalog.create_execution (new in SSIS 2012) is called to create an instance of an SSIS package execution.
• You pass the folder, project and package to the ssisdb.catalog.create_execution stored procedure.
• ssisdb.catalog.create_execution returns an output parameter named @execution_id; you need this value to start executing the SSIS package as well as to check the status of package execution.
• The ssisdb.catalog.start_execution stored procedure (new in SSIS 2012) is called to start the actual execution of the package.
• The SSIS package execution is asynchronous; i.e. when you call the ssisdb.catalog.start_execution stored procedure you are not waiting for the package to complete.

Execute the Stored Procedure

Launch SSMS, open a New Query, and run the following T-SQL script:

declare @output_execution_id bigint
exec dbo.execute_ssis_package_sample @output_execution_id output
print @output_execution_id

You have now executed an SSIS package from a stored procedure!

Create a Console Application

I will use a .NET console application to demonstrate executing an SSIS package from an application. The C# code below is standard ADO.NET which is the part of the .NET Framework that is used to work with relational databases. Essentially the code simply executes the execute_ssis_package_sample stored procedure described in the previous section.

The following is the “Main” section of the code (i.e. this is the code run when the console application is executed):

The following is the ExecSqlProc method called from Main; this is the code that calls the stored procedure that executes the SSIS package:

The ExecSqlProc method calls the GetSqlConnection method shown below to open a connection to the mssqltips database:

The connectionName parameter passed to the GetSqlConnection method must match a connection string in the app.config as shown below:

Check the Results

After running the .NET console application or running the execute-ssis-package-sample stored procedure, you can check the results of the SSIS package execution with the following query (where N is the output_execution_id returned as an output parameter by the ssisdb.catalog.create_execution stored procedure):

select status 
from SSISDB.catalog.executions
where execution_id = N

A status of 7 means the SSIS package ran successfully. A status of 2 indicates that the SSIS package is still running. There are 9 possible status values; see catalog.executions for the details.

The following method can be used to programmatically check the status (it is called from the Main code above; see the code following the “check the status” comment):

Next Steps

• SSIS 2012 has built-in stored procedures in the SSISDB database allowing you to easily execute an SSIS package from a stored procedure.
• While the above sample code is pretty straight forward, remember that the SSIS package execution happens asynchronously; i.e. your code that calls the stored procedure doesn’t wait for the SSIS package to finish. You can query the SSISDB.catalog.executions view to determine the status of the SSIS package execution.
• Download the sample SSIS project and .NET console application here to experiment on your own.